package com.pur.zsms.supplier.dao.impl;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.matermanage.mater.po.MaterLevelPo;
import com.pur.zsms.ordercustommanage.po.OrderSupplierMaterPo;
import com.pur.zsms.ordercustommanage.po.OrderSupplierPo;
import com.pur.zsms.ordercustommanage.vo.OrderCustFoodVo;
import com.pur.zsms.ordercustommanage.vo.OrderSupplierMaterVo;
import com.pur.zsms.ordercustommanage.vo.OrderSupplierVo;
import com.pur.zsms.supplier.dao.SupplierMaterOrderDao;

@Repository("supplierMaterOrderDao")
public class SupplierMaterOrderDaoImpl extends GenericDao implements SupplierMaterOrderDao {

	@Override
	public PaginationSupport listSupplierOrder(PageController pageController, OrderSupplierVo orderSupplierVo) throws GenericException {
		return this.findByNativeQuery("select * from t_order_supplier where ord_sup_id ='" + orderSupplierVo.getOrdSupId() + "'",
				OrderSupplierPo.class, pageController);
	}

	@Override
	public PaginationSupport listSupperOrderDetailed(PageController pageController, OrderSupplierMaterVo orderSupplierMaterVo, String oid)
			throws GenericException {
		return this
				.findByNativeQuery(
						"SELECT a.ord_id, a.ord_sup_id, a.ord_mater_id, SUM(a.ord_mater_weight), SUM(a.ord_mater_value), a.ord_mater_level,b.mater_name,c.ord_cust_id,d.real_name,IFNULL(f.ord_mater_weight,0),IFNULL(f.ord_mater_value,0),s.sup_companyname FROM t_supplier s,t_material b,t_order_supplier c,t_user d  ,t_order_supplier_mater a LEFT JOIN t_order_supplier_mater_reality f ON f.ord_id = a.ord_id AND a.ord_sup_id =f.ord_sup_id AND a.ord_mater_id = f.ord_mater_id AND a.ord_mater_level = f.ord_mater_level  WHERE  c.ord_id = a.ord_id AND a.ord_sup_id= c.ord_sup_id AND c.ord_cust_id = d.user_id and a.ord_mater_id = b.mater_id AND a.ord_sup_id = s.sup_id and  a.ord_id = '"
								+ oid + "' AND a.ord_sup_id = '" + orderSupplierMaterVo.getOrdSupId() + "' GROUP BY ord_mater_id ", pageController);
	}

	@Override
	public PaginationSupport listMaterToMaterDetailed(PageController pageController, OrderSupplierMaterVo orderSupplierMaterVo, String oid)
			throws GenericException {
		return this
				.findByNativeQuery(
						"SELECT a.ord_id, a.ord_mater_id, d.mater_name, a.ord_mater_weight, a.ord_mater_value, a.ord_mater_level, g.ord_cust_id, c.real_name, a.ord_sup_id, f.sup_companyname FROM t_order_supplier_mater a, t_user c, t_material d, t_supplier f, t_order_supplier g WHERE g.ord_id = a.ord_id AND a.ord_sup_id = f.sup_id AND g.ord_cust_id = c.user_id AND d.mater_id = a.ord_mater_id AND a.ord_sup_id = g.ord_sup_id AND a.ord_id = '"
								+ oid + "' AND a.ord_sup_id = '" + orderSupplierMaterVo.getOrdSupId() + "'", pageController);
	}

	@Override
	public PaginationSupport findMatertoMaterDetailedReal(PageController pageController, OrderSupplierMaterVo orderSupplierMaterVo, String oid)
			throws GenericException {
		return this
				.findByNativeQuery(
						"SELECT a.ord_id, a.ord_mater_id, d.mater_name, a.ord_mater_weight, a.ord_mater_value, a.ord_mater_level, g.ord_cust_id, c.real_name, a.ord_sup_id, f.sup_companyname FROM t_order_supplier_mater_reality a , t_material d, t_order_supplier g , t_supplier f, t_user c WHERE d.mater_id = a.ord_mater_id AND g.ord_cust_id = c.user_id AND a.ord_sup_id = f.sup_id AND a.ord_sup_id = g.ord_sup_id AND g.ord_id = a.ord_id AND a.ord_id = '"
								+ oid
								+ "' AND a.ord_sup_id = '"
								+ orderSupplierMaterVo.getOrdSupId()
								+ "' and a.ord_mater_weight != 0 and  a.ord_mater_weight IS NOT NULL ", pageController);
	}

	@Override
	public PaginationSupport listSelectMaterDetailed(PageController pageController, OrderSupplierMaterVo orderSupplierMaterVo, String oid)
			throws GenericException {
		return this
				.findByNativeQuery(
						"SELECT a.ord_id, a.ord_mater_id, d.mater_name, a.ord_mater_weight, a.ord_mater_value, a.ord_mater_level, g.ord_cust_id, c.shop_name, a.ord_sup_id, f.sup_companyname,a.ord_sup_mater_id,d.unit,d.specifications,d.brands,a.submit_ord_time,a.leave_message,d.`mater_type` from t_shop c, t_material d, t_supplier f, t_order_supplier g,t_order_supplier_mater a  WHERE g.ord_id = a.ord_id AND a.ord_sup_id = f.sup_id AND g.ord_cust_id = c.shop_id AND d.mater_id = a.ord_mater_id AND a.ord_sup_id = g.ord_sup_id AND a.ord_id = '"
								+ oid + "' ", pageController);
	}

	@Override
	public PaginationSupport listCustOrderDetailed(PageController pageController, OrderSupplierMaterVo orderSupplierMaterVo, String oid, String supId)
			throws GenericException {
		return this
				.findByNativeQuery(
						"SELECT a.ord_id, a.ord_sup_id, a.ord_mater_id, SUM(a.ord_mater_weight), SUM(a.ord_mater_value), a.ord_mater_level, b.mater_name, c.ord_cust_id, (SELECT d.shop_name FROM t_shop d WHERE c.ord_cust_id = d.shop_id ), (SELECT e.sup_companyname FROM t_supplier e WHERE e.sup_id = a.ord_sup_id ), a.ord_sup_mater_id, IFNULL(f.ord_mater_weight, 0), IFNULL(f.ord_mater_value, 0), b.unit, b.specifications, b.brands, f.return_goods, a.submit_ord_time, IFNULL(f.ord_mater_value_unit, 0), IFNULL(f.ord_mater_value_cost, 0), IFNULL( f.ord_mater_value_cost_unit,0), a.leave_message , (SELECT describ FROM t_code WHERE code_type_id = 'mater_type' AND code_oder = b.mater_type) AS matertype,c.`ord_date`,f.`period_value_flag` FROM t_material b, t_order_supplier c ,t_order_supplier_mater a ,t_order_supplier_mater_reality f WHERE a.ord_mater_id = b.mater_id AND a.ord_id = '"
								+ oid
								+ "' AND a.ord_sup_id = '"
								+ supId
								+ "' AND a.ord_sup_id = c.ord_sup_id AND c.ord_id = a.ord_id AND f.ord_id = a.ord_id AND a.ord_sup_id = f.ord_sup_id AND a.ord_mater_id = f.ord_mater_id AND a.ord_mater_level = f.ord_mater_level GROUP BY ord_mater_id ORDER BY b.mater_type,b.`all_spell`",
						pageController);
	}

	public List<Object[]> rowslistCustOrderDetailed(String oid, String supId) throws GenericException {
		List list = this.entityManager.createNativeQuery(
				"SELECT COUNT(1) FROM t_order_supplier_mater WHERE ord_id ='" + oid + "' AND ord_sup_id ='" + supId + "'  ").getResultList();
		return list;
	}

	@Override
	public PaginationSupport listSelectFoodDetailed(PageController pageController, OrderCustFoodVo orderCustFoodVo, String oid)
			throws GenericException {
		return this
				.findByNativeQuery(
						"SELECT o.ord_id,o.ord_food_id,o.ord_food_num,o.ord_food_value,a.food_name FROM t_order_custom_food o,t_food a  WHERE o.ord_food_id = a.food_id and ord_id =  '"
								+ oid + "'", pageController);
	}

	public void addOrderSupplierMater(OrderSupplierMaterPo po) throws GenericException {
		this.persist(po);
	}

	@Override
	public List<OrderSupplierMaterPo> getOrderCust(String ordId, String ordMaterId, String ordSupId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();

		ql.append(" select o ");
		ql.append(" from OrderSupplierMaterPo o where   o.ordMaterId = '" + ordMaterId + "' and o.ordId='" + ordId + "' and  o.ordSupId = '"
				+ ordSupId + "'");
		List<OrderSupplierMaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public MaterLevelPo getMaterLevelByMaterId(int materId, String level) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelPo o where o.materLevel = '" + level + "' and o.materId=" + materId);
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}
}
